﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace IQReportes.Models
{
    public class ConexionSQL
    {
        public SqlConnection connection { get; set; }
        private string cadenaConexion = System.Configuration.ConfigurationManager.ConnectionStrings["conexionPrincipalIQ"].ToString();

        private bool AbrirConexion()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (SqlException)
            {
                return false;
            }
        }

        private void CerrarConexion()
        {
            try
            {
                connection.Close();
            }
            catch (SqlException)
            {
                return;
            }
        }
        
        public void ejecutarComando(SqlCommand command)
        {
            connection = new SqlConnection(cadenaConexion);
            AbrirConexion();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                CerrarConexion();
                return;
            }
            CerrarConexion();
        }

        public SqlDataReader ejecutarReader(SqlCommand comando)
        {
            SqlDataReader dataReader;
            connection = new SqlConnection(cadenaConexion);
            AbrirConexion();
            try
            {
                dataReader = comando.ExecuteReader();
                CerrarConexion();
                return dataReader;
            }
            catch (Exception)
            {
                CerrarConexion();
                return null;
            }
        }

        public void ejecutarSelectProcedureDS(string nombreStoredProc, List<SqlParameter> parametros, DataSet ds)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            connection = new SqlConnection(cadenaConexion);
            da.SelectCommand = new SqlCommand(nombreStoredProc, connection);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            try
            {
                foreach (SqlParameter parametro in parametros)
                {
                    da.SelectCommand.Parameters.Add(parametro);
                }
            }
            catch(Exception e)
            {

            }

            AbrirConexion();
            try
            {
                da.Fill(ds);
            }
            catch(Exception e)
            {
                CerrarConexion();
            }
            CerrarConexion();
        }

        public void ejecturarNoReaderProcedure(List<SqlParameter> listaParametros, string nombreStored)
        {
            try
            {
                connection = new SqlConnection(cadenaConexion);
                SqlCommand cmd = new SqlCommand(nombreStored, connection);
                                
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 60000;

                foreach (SqlParameter p in listaParametros)
                {
                    cmd.Parameters.Add(p);
                }
                
                connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                connection.Close();
            }
            catch (Exception e)
            {
                throw ;
            }
        }

    }
}